package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Deptinfos;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.*;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Role;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.User;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ApprovalProcessInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.TongJiInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DepartmentInfo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;

/**
 * @author py
 * @Date
 */
@Controller
@RequestMapping("/reservation")
public class ReservationsRecordController extends BaseController {
    @Autowired
    IReservationsService reservationsService;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/marketing/reservations/";

    private String PREFIX1 = "/weixin/market/";

    /**
     * 微信
     * 跳转到市场新建患者
     */
    @RequestMapping("/market_addPatient/{num}")
    public String market_addPatient(@PathVariable String num, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        model.addAttribute("num", num);
        return PREFIX1 + "market_addPatient.html";
    }

    /**
     * 外呼
     * 跳转到外呼页面
     */
    @RequestMapping("/to_outbound/{customerPhone}/{id}")
    public String to_outbound(@PathVariable String customerPhone,@PathVariable String id, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        model.addAttribute("customerPhone", customerPhone);
        model.addAttribute("id", id);
        return PREFIX + "outbound.html";
    }

    /**
     * 外呼
     * 跳转到外呼页面
     */
    @RequestMapping("/to_outbounds/{customerPhone}/{id}")
    public String to_outbounds(@PathVariable String customerPhone,@PathVariable String id, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        model.addAttribute("customerPhone", customerPhone);
        model.addAttribute("id", id);
        return PREFIX + "outbounds.html";
    }

    /**
     * 来电详情
     * 跳转到外呼来电详情
     */
    @RequestMapping("/to_callDetails/{customerPhone}")
    public String to_callDetails(@PathVariable String customerPhone, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(customerPhone)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String phoneSql3 = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,a.* from reservations_info a " +
                " where (customer_phone like '%" + customerPhone + "%' or customer_phone2 like '%" + customerPhone + "%' )";
        List<ReservationsInfo> reservationsInfoList = sqlMapper.selectList(phoneSql3, ReservationsInfo.class);
        model.addAttribute("reservationsInfoList", reservationsInfoList);
        model.addAttribute("size", reservationsInfoList.size());
        model.addAttribute("customerPhone", customerPhone);
        return PREFIX + "callDetails.html";
    }

    /**
     * 微信
     * 跳转到患者上传影像
     */
    @RequestMapping("/market_patient")
    public String market_patient(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        return PREFIX1 + "market_patient.html";
    }

    @RequestMapping(value = "/reservation_import")
    public String to_import(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "reservation_import.html";
    }

    /**
     * 微信
     * 跳转到影像上传
     */
    @RequestMapping("/market_uploadImage")
    public String market_uploadImage(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        return PREFIX1 + "market_uploadImage.html";
    }

    /**
     * 微信
     * 跳转到审批分类
     */
    @RequestMapping("/market_approval")
    public String market_approval(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        return PREFIX1 + "market_approval.html";
    }

    /**
     * 微信
     * 跳转到审批详情
     */
    @RequestMapping("/market_approvalDetails")
    public String market_approvalDetails(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        return PREFIX1 + "market_approvalDetails.html";
    }

    /**
     * 微信
     * 跳转到审批信息
     */
    @RequestMapping("/market_approvalInformation")
    public String market_approvalInformation(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/weixin/login.html";
        }
        return PREFIX1 + "market_approvalInformation.html";
    }

//    -------------------   分割线  -----------------------

    /**
     * 跳转到市场预约  建档
     */
    @RequestMapping("/reservationsRecord")
    public String reservationsRecord(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservationsRecord.html";
    }

    /**
     * 跳转到市场预约  建档
     */
    @RequestMapping("/reservationsRecord2")
    public String reservationsRecord2(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservationsRecord2.html";
    }

    /**
     * 跳转到市场预约  已到
     */
    @RequestMapping("/reservationsArrived")
    public String reservationsArrived(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("sttleAccountsTime", ShiroKit.getUser().getSttleAccountsTime());
        return PREFIX + "reservations_arrived.html";
    }


    /**
     * 跳转到市场预约  已到 费用统计
     */
    @RequestMapping("/reservationsArrivedMoneyTotal")
    public String reservationsArrivedMoneyTotal(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("sttleAccountsTime", ShiroKit.getUser().getSttleAccountsTime());
        return PREFIX + "reservations_arrived_money.html";
    }

    /**
     * 跳转到市场预约  未到
     */
    @RequestMapping("/reservationsNoArrived")
    public String reservationsNoArrived(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservations_no_arrived.html";
    }

    /**
     * 跳转到市场预约  到店记录
     */
    @RequestMapping("/storeRecords")
    public String storeRecords(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservations_storeRecords.html";
    }

    /**
     * 添加预约客户
     */
    @RequestMapping(value = "/add")
    @ResponseBody
    public String add(@Valid ReservationsInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String getheadsql = "select firstconcats(account) as account from user where account='" + ShiroKit.getUser().getAccount() + "' limit 1";
        User user = sqlMapper.selectOne(getheadsql, User.class);
        String currentDate = DateUtil.getTime(new Date()).substring(0, 10);//取今天的年月日
        String sql = "select id from reservations_info where left(filing_time,19) = '" + currentDate + "'";
        List<ReservationsInfo> rList = sqlMapper.selectList(sql, ReservationsInfo.class);
        String reservationsNum = user.getAccount().replace("_", "") + currentDate.replace("-", "") + (rList.size() + 1);
        info.setReservationsNum(reservationsNum);
        if (info.getPhoneSharShip() == null || info.getPhoneSharShip() == "") {
            info.setPhoneSharShip("本人");
        } else {
            info.setPhoneSharShip(info.getPhoneSharShip());
        }
        if (!"本人".equals(info.getPhoneSharShip())) {
            //查询总表使用相同手机号集合
            List<ReservationsInfo> existList = reservationsService.getCustomerPhoneCounts(info.getCustomerPhone());
            //查询总表使用相同手机号人数
            int count = existList.size();
            String id = "yy" + CommUtils.getUUID();
            info.setCustomerPhone(info.getCustomerPhone() + "+" + count);
            info.setId(id);
            reservationsService.insert(info);
            reservationsService.insertsc(info); //添加到市场预约表
            return 1 + "#" + id;
        } else {
            //主号是否存在
            ReservationsInfo reservationsInfo = reservationsService.getCustomerPhoneCount(info.getCustomerPhone());
            //辅号是否存在，电话2
            ReservationsInfo reservationsInfo2 = reservationsService.getCustomerPhoneCount2(info.getCustomerPhone2());
            if (reservationsInfo != null || reservationsInfo2 != null) {
                return 0 + "#0";
            } else {
                String id = "yy" + CommUtils.getUUID();
                info.setId(id);
                reservationsService.insert(info);//添加到总表
                reservationsService.insertsc(info); //添加到市场预约表
                return 1 + "#" + id;
            }
        }
    }

    /**
     * 发起二次开发
     */
    @RequestMapping(value = "/updatetwoDevelopment/{id}", method = RequestMethod.POST)
    @ResponseBody
    public int secondDevelopment(@PathVariable String id) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //查询该患者是否已经被发起过二次开发
        String twoSql = "select id from reservations_second_development where reservations_id='" + id + "'";
        List<ApprovalProcessInfo> list = sqlMapper.selectList(twoSql, ApprovalProcessInfo.class);
        if (list.size() > 0) {
            return -1;
        } else {
            //通过预约id查询到过哪些店
            String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + id + "' ";
            List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
            String rechargeTables = "";
            if (abbreviationList.size() > 0) {
                for (int i = 0; i < abbreviationList.size(); i++) {
                    String head = abbreviationList.get(i);
                    if (i == abbreviationList.size() - 1) {
                        rechargeTables += "select id,operation_time,recharge_time,reservations_id from " + head + "recharge_info where reservations_id='" + id + "' ";
                    } else {
                        rechargeTables += "select id,operation_time,recharge_time,reservations_id from " + head + "recharge_info where reservations_id='" + id + "' union ";
                    }
                }
            } else {
                return 0;
            }
            String custimeSql = "select t1.id as id,t1.reservations_id as reservationsId, t1.recharge_time as rechargeTime,left(t1.operation_time,19) as operationTime " +
                    "from (" + rechargeTables + ") t1 where  t1.reservations_id = '" + id + "' group by t1.recharge_time order by t1.recharge_time desc limit 1";
            RechargeInfo info = sqlMapper.selectOne(custimeSql, RechargeInfo.class);
            if (info == null) {
                return 0;
            } else {
                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                //最后一次结账时间
                LocalDateTime lastDateTime = LocalDateTime.parse(info.getRechargeTime(), df);
                Long milliLastTime = lastDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli();
                //满三年的时间戳
                Long thTime = 94608000000L;
                Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
                if ((milliSecond.longValue() - milliLastTime.longValue()) > thTime.longValue()) {
                    return 1;
                } else {
                    return 2;
                }
            }
        }
    }


    /**
     * 跳转到市场预约  预约回访
     */
    @RequestMapping("/record")
    public String record(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("areaList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"areaList",0));
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "revisit_record.html";
    }

    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/reservations_addTo")
    public String reservations_addTo(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRefereeShipInfoList() == null) {
            String getListSql = "select id,referee_ship as refereeShip from referee_ship_info order by num asc;";
            List<RefereeShipInfo> refereeShipList = sqlMapper.selectList(getListSql, RefereeShipInfo.class);
            ShiroKit.getUser().setRefereeShipInfoList(refereeShipList);
        }
        if (ShiroKit.getUser().getEconomicAbilityList() == null) {
            String getListSql = "select id, economic_ability from economic_ability_info order by num asc;";
            List<EconomicAbilityInfo> economicAbilityList = sqlMapper.selectList(getListSql, EconomicAbilityInfo.class);
            ShiroKit.getUser().setEconomicAbilityList(economicAbilityList);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (ShiroKit.getUser().getCharactersList() == null) {
            String getListSql = "select id, characters from character_info order by num asc;";
            List<CharacterInfo> charactersList = sqlMapper.selectList(getListSql, CharacterInfo.class);
            ShiroKit.getUser().setCharactersList(charactersList);
        }
        if (ShiroKit.getUser().getOccupationInfoList() == null) {
            String getListSql = "select id, occupation from occupation_info where isshow=1 order by num asc;";
            List<OccupationInfo> occupationList = sqlMapper.selectList(getListSql, OccupationInfo.class);
            ShiroKit.getUser().setOccupationInfoList(occupationList);
        }
        if (ShiroKit.getUser().getConsumptionHabitsList() == null) {
            String getListSql = "select id, consumption_habits from consumption_habits_info order by  num asc;";
            List<ConsumptionHabitsInfo> consumptionHabitsList = sqlMapper.selectList(getListSql, ConsumptionHabitsInfo.class);
            ShiroKit.getUser().setConsumptionHabitsList(consumptionHabitsList);
        }
        model.addAttribute("consumptionHabitsList", ShiroKit.getUser().getConsumptionHabitsList());
        model.addAttribute("refereeShipList", ShiroKit.getUser().getRefereeShipInfoList());
        model.addAttribute("economicAbilityList", ShiroKit.getUser().getEconomicAbilityList());
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("charactersList", ShiroKit.getUser().getCharactersList());
        model.addAttribute("occupationList", ShiroKit.getUser().getOccupationInfoList());
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservations_addToNew.html";
    }


    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/reservations_addToTwo")
    public String reservations_addToTwo(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRefereeShipInfoList() == null) {
            String getListSql = "select id,referee_ship as refereeShip from referee_ship_info order by num asc;";
            List<RefereeShipInfo> refereeShipList = sqlMapper.selectList(getListSql, RefereeShipInfo.class);
            ShiroKit.getUser().setRefereeShipInfoList(refereeShipList);
        }
        if (ShiroKit.getUser().getEconomicAbilityList() == null) {
            String getListSql = "select id, economic_ability from economic_ability_info order by num asc;";
            List<EconomicAbilityInfo> economicAbilityList = sqlMapper.selectList(getListSql, EconomicAbilityInfo.class);
            ShiroKit.getUser().setEconomicAbilityList(economicAbilityList);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (ShiroKit.getUser().getCharactersList() == null) {
            String getListSql = "select id, characters from character_info order by num asc;";
            List<CharacterInfo> charactersList = sqlMapper.selectList(getListSql, CharacterInfo.class);
            ShiroKit.getUser().setCharactersList(charactersList);
        }
        if (ShiroKit.getUser().getOccupationInfoList() == null) {
            String getListSql = "select id, occupation from occupation_info where isshow=1 order by num asc;";
            List<OccupationInfo> occupationList = sqlMapper.selectList(getListSql, OccupationInfo.class);
            ShiroKit.getUser().setOccupationInfoList(occupationList);
        }
        if (ShiroKit.getUser().getConsumptionHabitsList() == null) {
            String getListSql = "select id, consumption_habits from consumption_habits_info order by  num asc;";
            List<ConsumptionHabitsInfo> consumptionHabitsList = sqlMapper.selectList(getListSql, ConsumptionHabitsInfo.class);
            ShiroKit.getUser().setConsumptionHabitsList(consumptionHabitsList);
        }
        model.addAttribute("consumptionHabitsList", ShiroKit.getUser().getConsumptionHabitsList());
        model.addAttribute("refereeShipList", ShiroKit.getUser().getRefereeShipInfoList());
        model.addAttribute("economicAbilityList", ShiroKit.getUser().getEconomicAbilityList());
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("charactersList", ShiroKit.getUser().getCharactersList());
        model.addAttribute("occupationList", ShiroKit.getUser().getOccupationInfoList());
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "reservations_addToNewTwo.html";
    }


    /**
     * 市场部预约管理建档
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object reservationsRecord(SearchReservationsInfo info, HttpServletRequest request) {
        Page<ReservationsInfo> page = new PageFactory<ReservationsInfo>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<ReservationsInfo> list = reservationsService.selectPageListByStatusMap(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = reservationsService.selectPageListByStatusMap(page, info);
            }
            page.setRecords(list);
            page.setTotal(reservationsService.getQueryPagesCount(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 市场部预约管理建档
     */
    @RequestMapping("/list2")
    @ResponseBody
    public Object reservationsRecord2(SearchReservationsInfo info, HttpServletRequest request) {
        Page<ReservationsInfo> page = new PageFactory<ReservationsInfo>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<ReservationsInfo> list = reservationsService.selectPageListByStatusMap2(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = reservationsService.selectPageListByStatusMap2(page, info);
            }
            page.setRecords(list);
            page.setTotal(reservationsService.getQueryPagesCount(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 市场部预约管理 已到记录 列表
     */
    @RequestMapping("/listYDjl")
    @ResponseBody
    public Object listYDjl(SearchReservationsInfo info, HttpServletRequest request) {
        Page<ReservationsInfo> page = new PageFactory<ReservationsInfo>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<ReservationsInfo> list = reservationsService.selectPageListByStatusMapYDjl(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = reservationsService.selectPageListByStatusMapYDjl(page, info);
            }
            page.setRecords(list);
            page.setTotal(reservationsService.getQueryPagesCountYDjl(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 统计查询
     */
    @RequestMapping(value = "/tongji", method = RequestMethod.GET)
    @ResponseBody
    public List<TongJiInfo> change(String param1, String param2, String param3, String param4, String param5, String param6, String param7, String param8,
                                   String param9, String param10, String param11, String param12, String param13, String param14, String param15) throws Exception {
        //1.获取当前日期，格式为yyyy-MM-dd hh:mm:ss
        String currentDate = DateUtil.getTime(new Date()).substring(0, 10);//取今天的年月日
        //获取昨天日期字符串 格式为yyyy-MM-dd hh:mm:ss
        String lastDate = DateUtil.getAfterDayDate("-1").substring(0, 10);//取昨日 的年月日
        //获取当月的字符串
        String currerntMonth = currentDate.substring(0, 7);//yyyy-mm
        //获取上月日期
        String lastMonth = DateUtil.getAfterDayMonth("-1").substring(0, 7);//取上月的年月
        String contitionInfo = "";
        if (StringUtil.isNotEmpty(param1) && !"请选择".equals(param1)) {
            String outpatientName = "";
            String outpatientName2[] = param1.split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.outpatient_name in ( '" + outpatientName + "') ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.follower in (select t1.account from user t1 where t1.roleid='" + param2 + "')";
        }
        if (StringUtils.isNotBlank(param3)) {
            contitionInfo += " and t.appointment_pay_service= '" + param3 + "' ";
        }
        if (StringUtils.isNotBlank(param4)) {
            contitionInfo += " and t.archiving= '" + param4 + "' ";
        }
        if (StringUtils.isNotBlank(param5)) {
            contitionInfo += " and t.follower= '" + param5 + "' ";
        }
        if (StringUtils.isNotBlank(param6)) {
            contitionInfo += " and t.access_disease= '" + param6 + "' ";
        }
        if (StringUtils.isNotBlank(param7)) {
            if (!"请选择".equals(param7)) {
                contitionInfo += " and t.one_channel= '" + param7 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param8)) {
            if (!"请选择".equals(param8)) {
                contitionInfo += " and t.two_channel= '" + param8 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param9)) {
            if (!"请选择".equals(param9)) {
                contitionInfo += " and t.thr_channel= '" + param9 + "' ";
            }
        }
        if (StringUtil.isNotEmpty(param10) && !"请选择".equals(param10)) {
            String statuss = "";
            String statuss2[] = param10.split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.statuss in ( '" + statuss + "') ";
        }
        if (StringUtils.isNotBlank(param11)) {
            contitionInfo += " and t.status= '" + param11 + "' ";
        }

        if (StringUtil.isNotEmpty(param14) && !"请选择".equals(param14)) {
            String area = "";
            String area2[] = param14.split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.outpatient_name in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "')) ";
        }
        //姓名   电话   微信  预约号
        if (StringUtils.isNotBlank(param15)) {
            contitionInfo += " and (t.customer_name like CONCAT('%" + param15 + "%') or t.customer_phone like CONCAT('%" + param15 + "%') " +
                    " or t.customer_phone2 like CONCAT('%" + param15 + "%') " +
                    " or t.wechat_number like CONCAT('%" + param15 + "%') or t.reservations_num like CONCAT('%" + param15 + "%') " +
                    " or t.identification_number like CONCAT('%" + param15 + "%') or t.key_word like CONCAT('%" + param15 + "%') " +
                    " or t.remarks like CONCAT('%" + param15 + "%') or t.huifang like CONCAT('%" + param15 + "%') " +
                    " or t.szm like CONCAT('%" + param15 + "%')) ";
        }
        contitionInfo += "  group by t.id) t ";
        //查询今日建档
        String sql = "select count(*) as countNum from (select id from reservations_infosc t where left(filing_time,10) = '" + currentDate + "' " + contitionInfo +
                //查询昨日建档
                " union all select count(*) as countNum from (select id  from reservations_infosc t where left(filing_time,10) = '" + lastDate + "' " + contitionInfo +
                //查询本周建档
                " union all select count(*) as countNum from (select id  from reservations_infosc t where filing_time >='" + param12 + "' and filing_time <='" + param13 + "' " + contitionInfo +
                //查询当月建档
                " union all select count(*) as countNum from (select id  from reservations_infosc t where left(filing_time,7) = '" + currerntMonth + "' " + contitionInfo +
                //查询上月建档
                " union all select count(*) as countNum from (select id from reservations_infosc t where left(filing_time,7) = '" + lastMonth + "' " + contitionInfo;
        List<TongJiInfo> tongJiInfoList = sqlMapper.selectList(sql, TongJiInfo.class);
        return tongJiInfoList;
    }

    /**
     * 选择建档人
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/to_archiving")
    public String to_archiving(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getRolelist() == null) {
            String selectsql = "select * from role a where a.pid=(select id from role a where a.name='市场') group by a.num;";
            List<Role> rolelist = sqlMapper.selectList(selectsql, Role.class);
            ShiroKit.getUser().setRolelist(rolelist);
        }
        model.addAttribute("rolelist", ShiroKit.getUser().getRolelist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("roleName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"RoleName",0));
        return PREFIX + "archiving.html";
    }

    /**
     * 表格筛选
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/reservation_screenTable/{menuName}")
    public String reservation_screenTable(@PathVariable String menuName, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        String userName = ShiroKit.getUser().getAccount();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String menuListManagement = menuName + "listManagement";
            if (ShiroKit.getSessionAttr(menuListManagement) == null) {
                String sql = "select * from list_management where menuName='" + menuName + "' limit 1";
                ListManagement listManagement = sqlMapper.selectOne(sql, ListManagement.class);
                ShiroKit.setSessionAttr(menuListManagement, listManagement);
            }
            if (ShiroKit.getSessionAttr(menuName) == null) {
                String sqlsel = "select * from list_management_account where account ='" + userName + "' and menuName='" + menuName + "' limit 1";
                ListManagement listEcho = sqlMapper.selectOne(sqlsel, ListManagement.class);
                if (listEcho != null) {
                    ShiroKit.setSessionAttr(menuName, listEcho.getListx1());
                    model.addAttribute("listEcho", ShiroKit.getSessionAttr(menuName));
                } else {
                    ListManagement listEcho1 = new ListManagement();
                    listEcho1.setListx1("");
                    model.addAttribute("listEcho", listEcho1);
                }
            } else {
                model.addAttribute("listEcho", ShiroKit.getSessionAttr(menuName));
            }
            model.addAttribute("listManagement", ShiroKit.getSessionAttr(menuListManagement));
            model.addAttribute("menuName", menuName);
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            return PREFIX + "reservations_screenTable.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 表格筛选
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/reservation_updateTable", method = RequestMethod.POST)
    @ResponseBody
    public int reservation_updateTable(String listx1, String menuName, String userName, Model model) {
        String sql = "select * from list_management_account where account ='" + userName + "' and menuName='" + menuName + "' limit 1";
        ListManagement listManagement = sqlMapper.selectOne(sql, ListManagement.class);
        ShiroKit.setSessionAttr(menuName, listx1);
        if (listManagement != null) {
            String sqlupdate = "update list_management_account set listx1='" + listx1 + "' , update_time=now() " +
                    " where account ='" + userName + "' and menuName='" + menuName + "'";
            return sqlMapper.update(sqlupdate);
        } else {
            String sqladd = "insert into list_management_account " +
                    " (account,menuName,listx1,add_time,update_time)  " +
                    " values ('" + userName + "','" + menuName + "','" + listx1 + "',now(),now())";
            return sqlMapper.insert(sqladd);
        }
    }

    /**
     * 表格加载
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/reservation_initialization", method = RequestMethod.POST)
    @ResponseBody
    public String reservation_initialization(String menuName, Model model) {
        String userName = ShiroKit.getUser().getAccount();
        if (ShiroKit.getSessionAttr(menuName) == null) {
            String sql = "select * from list_management_account where account ='" + userName + "' and menuName='" + menuName + "' limit 1";
            ListManagement listManagement = sqlMapper.selectOne(sql, ListManagement.class);
            if (listManagement == null) {
                ShiroKit.setSessionAttr(menuName, "");
            } else {
                ShiroKit.setSessionAttr(menuName, listManagement.getListx1());
            }
            return ShiroKit.getSessionAttr(menuName);
        } else {
            return ShiroKit.getSessionAttr(menuName);
        }
    }

    //建档管理导出
    @RequestMapping(value = "/reservationsExportJD")
    public void reservationsExportJD(@Valid SearchReservationsInfo searchInfo, HttpServletResponse response) {
        try {
            List<ReservationsInfo> list = reservationsService.reservationsExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 29; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("预约号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("手机号码");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("手机号码2");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("预约门诊");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("到店状态");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("回访状态");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("预约时间");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("初诊时间");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("建档人");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("跟进人");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("建档时间");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("回访");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("开单状态");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("预约病种");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("访问病种");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("关键词");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("微信号");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("备注");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                ReservationsInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 23; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getReservationsNum());
                row.getCell(1).setCellValue(map.getCustomerName());
                if (Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"PhonePrivilegesexp",0)+"") == 1) {
                    row.getCell(2).setCellValue(map.getCustomerPhone());
                    row.getCell(3).setCellValue(map.getCustomerPhone2());
                } else {
                    row.getCell(2).setCellValue("无");
                    row.getCell(3).setCellValue("无");
                }
                row.getCell(4).setCellValue(map.getOutpatientName());
                row.getCell(5).setCellValue(map.getStatus());
                row.getCell(6).setCellValue(map.getStatuss());
                row.getCell(7).setCellValue(map.getAppointmentTime());
                row.getCell(8).setCellValue(map.getFirstVisitDate());
                row.getCell(9).setCellValue(map.getArchiving());
                row.getCell(10).setCellValue(map.getFollower());
                row.getCell(11).setCellValue(map.getFilingTime());
                row.getCell(12).setCellValue(map.getOneChannel());
                row.getCell(13).setCellValue(map.getTwoChannel());
                row.getCell(14).setCellValue(map.getThrChannel());
                row.getCell(15).setCellValue(map.getHuifang());
                row.getCell(16).setCellValue(map.getSex());
                row.getCell(17).setCellValue(map.getCustomerAge());
                row.getCell(18).setCellValue(map.getDealStatus());
                row.getCell(19).setCellValue(map.getAppointmentPayService());
                row.getCell(20).setCellValue(map.getAccessDisease());
                row.getCell(21).setCellValue(map.getKeyWord());
                row.getCell(22).setCellValue(map.getWechatNumber());
                row.getCell(23).setCellValue(map.getRemarks());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //已到记录导出
    @RequestMapping(value = "/reservationsExportYDjl")
    public void reservationsExportYDjl(@RequestParam(required = false) String reach,
                                       @RequestParam(required = false) String appointmentPayService,
                                       @RequestParam(required = false) String archiving,
                                       @RequestParam(required = false) String follower,
                                       @RequestParam(required = false) String accessDisease,
                                       @RequestParam(required = false) String startFirstVisitDate,
                                       @RequestParam(required = false) String endFirstVisitDate,
                                       @RequestParam(required = false) String startFilingTime, @RequestParam(required = false) String startAppointmentTime,
                                       @RequestParam(required = false) String endFilingTime, @RequestParam(required = false) String endAppointmentTime,
                                       @RequestParam(required = false) String arrivalStore, @RequestParam(required = false) String pageType,
                                       @RequestParam(required = false) String oneChannel, @RequestParam(required = false) String status,
                                       @RequestParam(required = false) String twoChannel, @RequestParam(required = false) String thrChannel,
                                       @RequestParam(required = false) String wechatNumber, @RequestParam(required = false) String statuss,
                                       @RequestParam(required = false) String department, HttpServletResponse response) {
        try {
            SearchReservationsInfo searchInfo = new SearchReservationsInfo();
            searchInfo.setReach(reach);
            searchInfo.setPageType(pageType);
            searchInfo.setStatuss(statuss);
            searchInfo.setStatus(status);
            searchInfo.setStartFirstVisitDate(startFirstVisitDate);
            searchInfo.setEndFirstVisitDate(endFirstVisitDate);
            searchInfo.setAppointmentPayService(appointmentPayService);
            searchInfo.setArchiving(archiving);
            searchInfo.setAccessDisease(accessDisease);
            searchInfo.setStartFilingTime(startFilingTime);
            searchInfo.setEndFilingTime(endFilingTime);
            searchInfo.setArrivalStore(arrivalStore);
            searchInfo.setStartAppointmentTime(startAppointmentTime);
            searchInfo.setEndAppointmentTime(endAppointmentTime);
            searchInfo.setOneChannel(oneChannel);
            searchInfo.setTwoChannel(twoChannel);
            searchInfo.setThrChannel(thrChannel);
            searchInfo.setWechatNumber(wechatNumber);
            searchInfo.setDepartment(department);
            searchInfo.setFollower(follower);
            List<ReservationsInfo> list = reservationsService.reservationsExportYDjl(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
//            if("1".equals(pageType)){
            for (int i = 0; i < 26; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("电话号码");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("疾病类型");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("咨询内容");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("媒体来源");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("详细来源");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("搜索关键词");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("备注");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("客服");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("添加时间");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("接待医生");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("消费状态");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("消费项目");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("消费明细");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("回访时间");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("回访记录");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("医生点评");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("部门");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("到院状态");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("微信");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("跟进人");
            cell.setCellStyle(style);
            cell = row.createCell(24);
            cell.setCellValue("预约门诊");
            cell.setCellStyle(style);
            cell = row.createCell(25);
            cell.setCellValue("初诊时间");
            cell.setCellStyle(style);
            cell = row.createCell(26);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(27);
            cell.setCellValue("跟踪状态");
            cell.setCellStyle(style);
            cell = row.createCell(28);
            cell.setCellValue("预约号");
            cell.setCellStyle(style);
            cell = row.createCell(29);
            cell.setCellValue("归属人");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                ReservationsInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String reservationsNum = map.getReservationsNum();
                String customerName = map.getCustomerName();
                String customerPhone = map.getCustomerPhone();
                String remarks = map.getRemarks();
                wechatNumber = map.getWechatNumber();
                status = map.getStatus();
                statuss = map.getStatuss();
                String appointmentTime = map.getAppointmentTime();
                String huifang = map.getHuifang();
                String jiedaiContent = map.getJiedaiContent();
                archiving = map.getArchiving();
                String filingTime = map.getFilingTime();
                String keyWord = map.getKeyWord();
                String firstVisitDate = map.getFirstVisitDate();  //首次到店时间
                oneChannel = map.getOneChannel();
                twoChannel = map.getTwoChannel();
                thrChannel = map.getThrChannel();
                String sex = map.getSex();
                follower = map.getFollower();
                String belonger = map.getBelonger();
                Double money = 0.00;
                if (map.getMoney() != null) {
                    money = map.getMoney();
                }
                String tcName = map.getTcName();
                int customerAge = 0;
                if (map.getCustomerAge() != null) {
                    customerAge = map.getCustomerAge();
                }
                String dealStatus = map.getDealStatus();
                appointmentPayService = map.getAppointmentPayService();
                String accessDiseases = map.getAccessDisease();
                arrivalStore = map.getArrivalStore();
                String expenditureProject = map.getExpenditureProject();
                department = map.getDepartment();
                String id = map.getId();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 8; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(customerName);
                row.getCell(1).setCellValue(sex);
                row.getCell(2).setCellValue(customerAge);
                row.getCell(3).setCellValue(customerPhone);
                row.getCell(4).setCellValue(appointmentPayService);
                row.getCell(5).setCellValue(accessDiseases);
                row.getCell(6).setCellValue(twoChannel);
                row.getCell(7).setCellValue(thrChannel);
                row.getCell(8).setCellValue(keyWord);
                row.getCell(9).setCellValue(remarks);
                row.getCell(10).setCellValue(archiving);
                row.getCell(11).setCellValue(firstVisitDate);
                row.getCell(12).setCellValue(filingTime);
                row.getCell(13).setCellValue(tcName);
                row.getCell(14).setCellValue(dealStatus);
                row.getCell(15).setCellValue(expenditureProject);
                row.getCell(16).setCellValue(money);
                row.getCell(17).setCellValue(appointmentTime);
                row.getCell(18).setCellValue(huifang);
                row.getCell(19).setCellValue(jiedaiContent);
                row.getCell(20).setCellValue(department);
                row.getCell(21).setCellValue(status);
                row.getCell(22).setCellValue(wechatNumber);
                row.getCell(23).setCellValue(follower);
                row.getCell(24).setCellValue(arrivalStore);
                row.getCell(25).setCellValue(firstVisitDate);
                row.getCell(26).setCellValue(oneChannel);
                row.getCell(27).setCellValue(statuss);
                row.getCell(28).setCellValue(reservationsNum);
                row.getCell(29).setCellValue(belonger);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

